/*
* ClassroomDaoImpl.java
* Created on  2014-8-5 下午4:48
* 版本       修改时间          作者      修改内容
* V1.0.1    2014-8-5       fengxuanzhen    初始版本
*
*/
package com.genghis.ptas.classroom.dao.impl;

import com.genghis.core.page.dao.PageDao;
import com.genghis.core.page.entity.Page;
import com.genghis.core.page.entity.PageParameter;
import com.genghis.ptas.classroom.dao.ClassroomDao;
import com.genghis.ptas.classroom.entity.ClassroomCourse;
import com.genghis.ptas.classroom.entity.ClassroomInfo;
import com.genghis.ptas.classroom.entity.ClassroomStudent;
import com.genghis.ptas.task.entity.LibraryOption;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 类的描述信息
 *
 * @author fengxuanzhen
 * @version 1.0.1
 */
@Repository("classroomDao")
public class ClassroomDaoImpl extends PageDao implements ClassroomDao {
    @Override
    public Page<ClassroomInfo> findClassroomInfoPageList(PageParameter pageParameter, ClassroomInfo classroomInfo) {
        StringBuilder sql = new StringBuilder("SELECT ci.*,ei.examination_name FROM classroom_info ci, examination_info ei WHERE ci.examination_id = ei.id ");
        if (classroomInfo.getName() != null && !classroomInfo.getName().equals("")) {
            sql.append(" AND name like '%").append(classroomInfo.getName().trim()).append("%'");
        }
        return queryForPage(pageParameter, sql.toString(), new BeanPropertySqlParameterSource(classroomInfo), new BeanPropertyRowMapper<ClassroomInfo>(ClassroomInfo.class));
    }

    @Override
    public void deleteClassroomInfoById(Integer id) {
        final String sql = "DELETE FROM classroom_info WHERE id = ?";
        getJdbcTemplate().update(sql, id);
    }

    @Override
    public void deleteClassroomCourseByClassroomId(Integer id) {
        final String sql = "DELETE FROM classroom_course WHERE classroom_id = ?";
        getJdbcTemplate().update(sql, id);
    }

    @Override
    public int addClassroomInfo(ClassroomInfo classroomInfo) {
        String sql = "INSERT INTO classroom_info(name,examination_id,pass_score,plan_time,memo) " +
                "VALUES(:name, :examinationId, :passScore, :planTime, :memo)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(classroomInfo), keyHolder);
        return keyHolder.getKey().intValue();
    }

    @Override
    public ClassroomInfo findClassroomInfoById(int taskId) {
        String sql = "SELECT ci.*,ei.examination_name FROM classroom_info ci, examination_info ei WHERE ci.examination_id = ei.id and ci.id = ?";
        return getJdbcTemplate().queryForObject(sql, new BeanPropertyRowMapper<ClassroomInfo>(ClassroomInfo.class), taskId);
    }

    @Override
    public List<LibraryOption> findLibraryOptionByTaskId(int taskId) {
        String sql = "SELECT * FROM library_option WHERE task_id = ?";
        return getJdbcTemplate().query(sql, new BeanPropertyRowMapper<LibraryOption>(LibraryOption.class), taskId);
    }

    @Override
    public void editClassroomInfo(ClassroomInfo classroomInfo) {
        String sql = "UPDATE classroom_info SET name = :name, examination_id = :examinationId , pass_score = :passScore," +
                " plan_time = :planTime, memo = :memo WHERE id = :id";
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(classroomInfo));
    }

    @Override
    public List<ClassroomCourse> findClassroomCourseByClassroomInfoId(Integer classroomInfoId) {
        String sql = "SELECT cc.*, c.course_name,c.photo_url FROM classroom_course cc, course c WHERE cc.course_id = c.id AND cc.classroom_id = ?";
        return getJdbcTemplate().query(sql, new BeanPropertyRowMapper<ClassroomCourse>(ClassroomCourse.class), classroomInfoId);
    }

    @Override
    public int addClassroomCourse(ClassroomCourse classroomCourse) {
        String sql = "INSERT INTO classroom_course(course_id,classroom_id, course_number) " +
                "VALUES(:courseId, :classroomId, :courseNumber)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(classroomCourse), keyHolder);
        return keyHolder.getKey().intValue();
    }

    @Override
    public List<ClassroomInfo> findAllClassroomInfoList() {
        String sql = "SELECT ci.*,ei.examination_name FROM classroom_info ci, examination_info ei WHERE ci.examination_id = ei.id";
        return getJdbcTemplate().query(sql, new BeanPropertyRowMapper<ClassroomInfo>(ClassroomInfo.class));
    }

    @Override
    public int addClassroomStudent(ClassroomStudent classroomStudent) {
        String sql = "INSERT INTO classroom_student(student_id,classroom_course_id, classroom_info_id) " +
                "VALUES(:studentId, :classroomCourseId, :classroomInfoId)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(classroomStudent), keyHolder);
        return keyHolder.getKey().intValue();
    }

    @Override
    public List<ClassroomStudent> findClassroomStudentByStudentIdAndClassroomInfoId(int userId, int classroomInfoId) {
        String sql = "SELECT cs.*,cc.course_number FROM classroom_student cs LEFT JOIN classroom_course cc " +
                "ON cs.classroom_course_id = cc.id WHERE cs.student_id = ? AND cs.classroom_info_id = " + classroomInfoId;
        return getJdbcTemplate().query(sql, new BeanPropertyRowMapper<ClassroomStudent>(ClassroomStudent.class), userId);
    }

    @Override
    public void changeStudentCourseId(ClassroomStudent classroomStudent) {
        String sql = "UPDATE classroom_student SET classroom_course_id = :classroomCourseId WHERE student_id = :studentId AND classroom_info_id = :classroomInfoId";
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(classroomStudent));
    }

    @Override
    public List<ClassroomInfo> findExpendClassroom(String classroomCourseName) {
        String sql = "SELECT * FROM classroom_info WHERE NAME LIKE '%" + classroomCourseName + "%'";
        return getJdbcTemplate().query(sql, new BeanPropertyRowMapper<ClassroomInfo>(ClassroomInfo.class));
    }

    @Override
    public List<ClassroomStudent> findClassroomStudentByClassroomInfoId(int classroomInfoId) {
        String sql = "SELECT cs.*,IFNULL(cc.course_number,0) course_number FROM classroom_student cs LEFT JOIN classroom_course cc " +
                "ON cs.classroom_course_id = cc.id WHERE cs.classroom_info_id =? ";
        return getJdbcTemplate().query(sql, new BeanPropertyRowMapper<ClassroomStudent>(ClassroomStudent.class), classroomInfoId);
    }

    @Override
    public Integer findMaxCourseNumberByCourseId(Integer id) {
        String sql = "SELECT COUNT(*) course_number FROM `classroom_course` WHERE `classroom_id` = ?";
        return getJdbcTemplate().queryForObject(sql, new BeanPropertyRowMapper<ClassroomCourse>(ClassroomCourse.class), id).getCourseNumber();
    }
}
